import time
import numpy as np
import pymysql
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import silhouette_score

# -------------------- 数据库数据处理类 --------------------
class DatabaseProcessor:
    def __init__(self):
        # 数据库连接配置
        self.connection = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            database='scenic',
            port=3306,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def process_tourist_data(self):
        """处理游客数据并生成统计结果"""
        try:
            # 执行SQL查询
            with self.connection.cursor() as cursor:
                sql = """
                SELECT 
                    t.tourist_agency_name, 
                    rel.id_no, 
                    LEFT(rel.id_no, 2) as province_code, 
                    DAYOFWEEK(gate.create_time) as day_of_week,
                    CAST(SUBSTRING(rel.id_no, 7, 4) AS UNSIGNED) as birth_year 
                FROM ticket_order_user_rel rel
                JOIN ticket_order t ON t.id = rel.order_id
                JOIN order_user_gate_rel gate ON gate.ticket_rel_id = rel.id
                WHERE t.tourist_agency_name != '' AND t.pay_time != ''
                """
                cursor.execute(sql)
                results = cursor.fetchall()
            
            # 转换为DataFrame
            df = pd.DataFrame(results)
            
            # 数据清洗与特征工程
            # 标记非周末访问
            df['is_weekday'] = df['day_of_week'].apply(lambda x: 1 if x not in [1, 7] else 0)
            
            # 标记有效身份证
            df['valid_id'] = df['id_no'].apply(lambda x: 1 if x and str(x).strip() != "" else 0)
            
            # 计算老年人比例 (年龄>=60)
            df['elderly'] = df.apply(
                lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
                axis=1
            )
            
            # 计算外省游客比例 (非广东省)
            df['out_province'] = df.apply(
                lambda x: 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
                axis=1
            )
            
            # 按旅行社分组聚合统计
            result = df.groupby(['tourist_agency_name']).agg(
                total_visitors=('id_no', 'count'),           # 总游客数
                valid_visitors=('valid_id', 'sum'),          # 有效身份证游客数
                weekday_visits=('is_weekday', 'sum'),        # 非周末访问次数
                out_province_visitors=('out_province', 'sum'), # 外省游客数
                elderly_visitors=('elderly', 'sum')          # 老年游客数
            )
            
            # 计算比例指标
            result['weekday_ratio'] = result['weekday_visits'] / result['total_visitors']
            result['out_province_ratio'] = result['out_province_visitors'] / result['valid_visitors'].replace(0, np.nan)
            result['elderly_ratio'] = result['elderly_visitors'] / result['valid_visitors'].replace(0, np.nan)
            
            # 清理中间列并处理缺失值
            result = result.drop(['weekday_visits', 'out_province_visitors', 'elderly_visitors'], axis=1)
            result = result.fillna(0)
            
            # 保存结果
            result.to_csv('scenic_data.csv')
            print(f"数据处理完成，结果已保存至 scenic_data.csv，共{len(result)}条记录")
            
            return result
            
        except Exception as e:
            print(f"数据处理出错: {e}")
            return None

# -------------------- 聚类分析类 --------------------
class TouristClusterAnalyzer:
    def __init__(self):
        try:
            # 加载数据
            self.df = pd.read_csv('./scenic_data.csv')
        except FileNotFoundError:
            print("错误：找不到数据文件 'scenic_data.csv'，请先运行数据处理程序")
            self.df = None
    
    def find_optimal_clusters(self):
        """使用轮廓系数寻找最优聚类数量"""
        if self.df is None:
            return
            
        try:
            # 提取特征并标准化
            features = self.df[['weekday_ratio', 'out_province_ratio', 'elderly_ratio']]
            scaler = StandardScaler()
            scaled_features = scaler.fit_transform(features)
            
            # 计算不同聚类数量的轮廓系数
            silhouette_scores = []
            for k in range(2, 6):
                kmeans = KMeans(n_clusters=k, random_state=42)
                cluster_labels = kmeans.fit_predict(scaled_features)
                score = silhouette_score(scaled_features, cluster_labels)
                silhouette_scores.append(score)
                print(f"聚类数量 k={k}，轮廓系数: {score:.4f}")
            
            # 可视化结果
            plt.figure(figsize=(10, 6))
            plt.plot(range(2, 6), silhouette_scores, marker='o')
            plt.title('K值与轮廓系数关系')
            plt.xlabel('聚类数量 (k)')
            plt.ylabel('轮廓系数')
            plt.grid(True)
            plt.savefig('silhouette_scores.png')
            plt.show()
            
            # 返回最佳聚类数
            best_k = range(2, 6)[np.argmax(silhouette_scores)]
            print(f"建议最佳聚类数量: {best_k}")
            return best_k
            
        except Exception as e:
            print(f"聚类分析出错: {e}")
            return None

# -------------------- 主程序入口 --------------------
if __name__ == '__main__':
    # 1. 数据处理
    db_processor = DatabaseProcessor()
    processed_data = db_processor.process_tourist_data()
    
    # 2. 聚类分析
    if processed_data is not None:
        cluster_analyzer = TouristClusterAnalyzer()
        optimal_k = cluster_analyzer.find_optimal_clusters()